<?php
class Transport_db extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }
    
    function is_areaname_unique($areaname, $id = "") {
        $where = "(area_name='".$areaname."')"; 
        $where .= ($id != "") ? " and area_id != '$id'" : "";
        $this->db->where($where);
        $result = $this->db->count_all_results('area');
        if($result>0)
            return false;
        else
            return true; 
    }
    
    function is_routename_unique($routename, $id = "") {
        $where = "(route_name='".$routename."')"; 
        $where .= ($id != "") ? " and route_id != '$id'" : "";
        $this->db->where($where);
        $result = $this->db->count_all_results('route');
        //print_object($divisionlabel); exit;
        if($result>0)
            return false;
        else
            return true; 
    }
    
    function is_term_unique($termname) {
        $where = "(term_name='".$termname."')"; 
        $this->db->where($where);
        $result = $this->db->count_all_results('term');
        if($result>0)
            return false;
        else
            return true;
    }
    
    function save_area($data,$pk='') {
        $save_result = array();
        
        if ($pk=='') {
    		$this->db->insert('area', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		} else {
    		$this->db->where('area_id', $pk);
    		$this->db->update('area', $data);
            //print_object($this->db->last_query());exit;
            $save_result['affected_rows'] = $this->db->affected_rows();
            if($save_result['affected_rows'] == -1 ) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}   
        return $save_result;
    }
    
    function save_route($data,$pk='') {
        $save_result = array();
        
        if ($pk=='') {
    		$this->db->insert('route', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		} else {
    		$this->db->where('route_id', $pk);
    		$this->db->update('route', $data);
            //print_object($this->db->last_query());exit;
            $save_result['affected_rows'] = $this->db->affected_rows();
            if($save_result['affected_rows'] == -1 ) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}   
        return $save_result;
    }
    
    function save_route_area($arealist, $routeid) {
        $this->db->delete('routearea', array('route_id' => $routeid)); 
        foreach($arealist as $areaid) {
            $this->db->set('route_id', $routeid);
            $this->db->set('area_id', $areaid);
            $this->db->insert('routearea');
        }
    }
    
    function get_area_list($orderby="") {
        if($orderby!="") {
            $this->db->order_by($orderby);
        }
        $query = $this->db->get('area');
        $result = $query->result_array();
        return $result;
    }
    
    function get_vh_list($where='') {
        $query = $this->db->get('vehicle');
        $result = $query->result_array();
        return $result;
    }
    
    function get_vehiclestafftype() {
        $query = $this->db->get('vehiclestafftype');
        $result = $query->result_array();
        return $result;
    }
    
    function get_staffstatus() {
        $query = $this->db->get('staffstatus');
        $result = $query->result_array();
        return $result;
    }        
    
    function save_staff($data,$pk='') {
        $save_result = array();
        
        if ($pk=='') {
    		$this->db->insert('vehiclestaff', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		} else {
    		$this->db->where('vs_id', $pk);
    		$this->db->update('vehiclestaff', $data);
            //print_object($this->db->last_query());exit;
            $save_result['affected_rows'] = $this->db->affected_rows();
            if($save_result['affected_rows'] == -1 ) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}   
        return $save_result;
    }
    
    function save_vehicle($data,$pk='') {
        $save_result = array();
        
        if ($pk=='') {
    		$this->db->insert('vehicle', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		} else {
    		$this->db->where('vh_id', $pk);
    		$this->db->update('vehicle', $data);
            //print_object($this->db->last_query());exit;
            $save_result['affected_rows'] = $this->db->affected_rows();
            if($save_result['affected_rows'] == -1 ) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}   
        return $save_result;
    }
    
    function get_staff_meta() {
        $meta = array(
            "table"=>"vehiclestaff",
            "fields" => array(
                "vs_staff_type" => array(
                    "label"=>"Staff Type",
                    "type"=>"ref",
                    "ref"=>array(
                        "table"=>"vehiclestafftype",
                        "pk"=>"vst_id",
                        "value"=>"vst_name"
                    )
               ),
               "vs_name" => array(
                    "label"=>"Name",
                    "type"=>"string"
               ),
               "vs_joining_date" => array(
                    "label"=>"Joining Date",
                    "type"=>"date"
               ),
               "vs_dob" => array(
                    "label"=>"Date of Birth",
                    "type"=>"date"
               ),
               "vs_rp_no" => array(
                    "label"=>"RP No.",
                    "type"=>"string"
               ),
               "vs_rp_expiry" => array(
                    "label"=>"RP Expiry",
                    "type"=>"date"
               ),
               "vs_address" => array(
                    "label"=>"Address",
                    "type"=>"string"
               ),
               "vs_mobile" => array(
                    "label"=>"Mobile",
                    "type"=>"string"
               ),
               "vs_phone" => array(
                    "label"=>"Telephone",
                    "type"=>"string"
               ),
               "vs_email" => array(
                    "label"=>"Email",
                    "type"=>"string"
               ),
               "vs_status" => array(
                    "label"=>"Status",
                    "type"=>"ref",
                    "ref"=>array(
                        "table"=>"staffstatus",
                        "pk"=>"ss_id",
                        "value"=>"ss_name"
                    )
               )
            )
        );
        
        return $meta;
    }
    
    function get_vehicle_meta() {
        $meta = array(
            "table"=>"vehicle",
            "fields" => array(
               "vh_no" => array(
                    "label"=>"Vehicle No.",
                    "type"=>"string"
               ),
               "vh_reg_no" => array(
                    "label"=>"Reg. No.",
                    "type"=>"string"
               ),
               "vh_reg_expiry" => array(
                    "label"=>"Reg. Expiry",
                    "type"=>"date"
               ),
               "vh_first_reg_date" => array(
                    "label"=>"First Reg. Date",
                    "type"=>"date"
               ),
               "vh_make" => array(
                    "label"=>"Make",
                    "type"=>"string"
               ),
               "vh_model" => array(
                    "label"=>"Model",
                    "type"=>"string"
               ),
               "vh_capacity" => array(
                    "label"=>"Capacity",
                    "type"=>"string"
               ),
               "vh_owner" => array(
                    "label"=>"Owner",
                    "type"=>"string"
               ),
               "vh_owner_tel" => array(
                    "label"=>"Owner Tel.",
                    "type"=>"string"
               ),
               "vh_route" => array(
                    "label"=>"Route",
                    "type"=>"ref",
                    "ref"=>array(
                        "table"=>"route",
                        "pk"=>"route_id",
                        "value"=>"route_name"
                    )
               ),
               "vh_driver_name" => array(
                    "label"=>"Driver",
                    "type"=>"string"
               ),
               "vh_helper_name" => array(
                    "label"=>"Helper",
                    "type"=>"string"
               ),
               "vh_status" => array(
                    "label"=>"Status",
                    "type"=>"ref",
                    "ref"=>array(
                        "table"=>"staffstatus",
                        "pk"=>"ss_id",
                        "value"=>"ss_name"
                    )
               )
            )
        );
        
        return $meta;
    }
    
    function get_area_meta() {
        $meta = array(
            "table"=>"area",
            "fields" => array(
               "area_name" => array(
                    "label"=>"Area Name",
                    "type"=>"string"
               ),
               "area_code" => array(
                    "label"=>"Area Code",
                    "type"=>"string"
               ),
               "area_transport_fee" => array(
                    "label"=>"Transport Fee",
                    "type"=>"string"
               )
            )
        );
        
        return $meta;
    }
    
    function get_route_meta() {
        $meta = array(
            "table"=>"route",
            "fields" => array(
               "route_name" => array(
                    "label"=>"Route Name",
                    "type"=>"string"
               )
            )
        );
        
        return $meta;
    }
    
    function get_vs_filter_count($filterdata='') {
        $this->db->from('vehiclestaff vs');
        $this->db->join('vehiclestafftype vst','vs.vs_staff_type = vst.vst_id','left');
        $this->db->join('staffstatus ss','vs.vs_status = ss.ss_id','left');
        $this->db->join('country ct','vs.vs_nationality = ct.id','left');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        return $this->db->count_all_results();
    }
    
    function get_vh_filter_count($filterdata='') {
        $this->db->from('vehicle vh');
        $this->db->join('route ru','vh.vh_route = ru.route_id','left');
        $this->db->join('staffstatus ss','vh.vh_status = ss.ss_id','left');
        $this->db->join('vehiclestaff vsd','vh.vh_driver = vsd.vs_id','left');   //Driver
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        return $this->db->count_all_results();
    }
    
    function get_area_filter_count($filterdata='') {
        $this->db->from('area ar');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        return $this->db->count_all_results();
    }
    
    function get_route_filter_count($filterdata='') {
        $this->db->from('route ru');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        return $this->db->count_all_results();
    }
    
    function get_vs_filter_list($limit=0,$start=0,$filterdata='',$include_deleted=true) {
        $this->db->from('vehiclestaff vs');
        $this->db->join('vehiclestafftype vst','vs.vs_staff_type = vst.vst_id','left');
        $this->db->join('staffstatus ss','vs.vs_status = ss.ss_id','left');
        $this->db->join('country ct','vs.vs_nationality = ct.id','left');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        $this->db->limit($limit, $start); 
        $query = $this->db->get();
        //print_object($this->db->last_query());
        return $query->result_array();
    }
    
    function get_vh_filter_list($limit=0,$start=0,$filterdata='',$include_deleted=true) {
        $this->db->select('vh.*, ru.*, ss.*, vsd.vs_name as vh_driver_name, vsh.vs_name as vh_helper_name');
        
        $this->db->from('vehicle vh');
        $this->db->join('route ru','vh.vh_route = ru.route_id','left');
        $this->db->join('staffstatus ss','vh.vh_status = ss.ss_id','left');
        $this->db->join('vehiclestaff vsd','vh.vh_driver = vsd.vs_id','left');   //Driver
        $this->db->join('vehiclestaff vsh','vh.vh_helper = vsh.vs_id','left');   //Helper
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        $this->db->limit($limit, $start); 
        $query = $this->db->get();
        //print_object($this->db->last_query());
        return $query->result_array();
    }
    
    function get_area_filter_list($limit=0,$start=0,$filterdata='',$include_deleted=true) {
        $this->db->from('area ar');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        $this->db->limit($limit, $start); 
        $query = $this->db->get();
        //print_object($this->db->last_query());
        return $query->result_array();
    }
    
    
    function get_route_filter_list($limit=0,$start=0,$filterdata='',$include_deleted=true) {
        $this->db->from('route');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        $this->db->limit($limit, $start); 
        $query = $this->db->get();
        //print_object($this->db->last_query());
        return $query->result_array();
    }
    
    function add_dg_col($col,$meta, &$cols) {
        $label = $meta['label'];
        $type = $meta['type'];
        $enum = array();
        if($type == 'enum') {
            $enum = $meta['enum'];
        }
        
        if($type == 'ref') {
            $type = "string";
            $col = $meta['ref']['value'];
        }
        
        $cols[$col] = array('label'=>$label,'type'=>$type, 'enum'=>$enum,'visible'=>true);
        //print_object($cols);
    }
    
    function build_vs_dg_data($fiterdata='') {
        $result['dg_title'] = "Bus Staff List";
        if($this->input->post('show_all')) {
            $result['show_all'] = true;
        } else {
            //$result['show_all'] = false;
            $result['current_page'] = ($this->input->post('page')) ? $this->input->post('page') : 1;
            $result['rows_per_page'] = ($this->config->item('per_page')) ? $this->config->item('per_page') : 10;
        }
        if($fiterdata=='')
            $result['total'] = $this->get_vs_filter_count();
        else
            $result['total'] = $this->get_vs_filter_count($fiterdata);
            
        if($result['show_all']) {
            $result['current_page'] = 1;
            $result['rows_per_page'] = ($result['total']==0)?100:$result['total'];
        }
        
        $result['pk'] = 'vs_id';
        $result['itemname'] = 'vs_name';
        //print_object($fiterdata); exit;
        if(is_array($fiterdata['cols']) && count($fiterdata['cols']) > 0) {
            $dgcols = array(
                'vs_id'=>array('label'=>'Staff ID','type'=>'link','visible'=>true, 'href'=>'/transport/viewvehiclestaff')
            );
            foreach($fiterdata['cols'] as $col) {
                $dgcol = $this->add_dg_col($col,$fiterdata['meta']['fields'][$col],$dgcols);
            }
            $result['dgcolumns'] = $dgcols;
        } else {
            $result['dgcolumns'] = array(
                'vs_id'=>array('label'=>'Staff ID','type'=>'link','visible'=>true, 'href'=>'/transport/viewvehiclestaff'),
                'vs_name'=>array('label'=>'Name','type'=>'string','visible'=>true),
                'vst_name'=>array('label'=>'Staff Type','type'=>'string','visible'=>true),
                'vs_address'=>array('label'=>'Address','type'=>'string','visible'=>true), 
                'vs_mobile'=>array('label'=>'Mobile','type'=>'string','visible'=>true),
                'name'=>array('label'=>'Nationality','type'=>'string','visible'=>true)           
            );
        }        
        
        $result['actions'] = array(
            'edit'=>'/transport/editvehiclestaff',
            'view'=>'/transport/viewvehiclestaff',
            'page'=>'/transport/vehiclestafflist'
        );
        
        $offset = $result['rows_per_page']*($result['current_page']-1);
        $result['pages_total'] =  ceil($result['total']/$result['rows_per_page']);
        if($fiterdata=='')
            $result["dgitems"] = $this->get_vs_filter_list($result['rows_per_page'], $offset, '');
        else
            $result["dgitems"] = $this->get_vs_filter_list($result['rows_per_page'], $offset, $fiterdata);
        return $result;
    }
    
    function build_vh_dg_data($fiterdata='') {
        $result['dg_title'] = "Vehicle List";
        if($this->input->post('show_all')) {
            $result['show_all'] = true;
        } else {
            //$result['show_all'] = false;
            $result['current_page'] = ($this->input->post('page')) ? $this->input->post('page') : 1;
            $result['rows_per_page'] = ($this->config->item('per_page')) ? $this->config->item('per_page') : 10;
        }
        if($fiterdata=='')
            $result['total'] = $this->get_vh_filter_count();
        else
            $result['total'] = $this->get_vh_filter_count($fiterdata);
            
        if($result['show_all']) {
            $result['current_page'] = 1;
            $result['rows_per_page'] = ($result['total']==0)?100:$result['total'];
        }
        
        $result['pk'] = 'vh_id';
        $result['itemname'] = 'vh_no';
        //print_object($fiterdata); exit;
        if(is_array($fiterdata['cols']) && count($fiterdata['cols']) > 0) {
            $dgcols = array(
                'vh_id'=>array('label'=>'Vehicle ID','type'=>'link','visible'=>true, 'href'=>'/transport/viewvehicle')
            );
            foreach($fiterdata['cols'] as $col) {
                $dgcol = $this->add_dg_col($col,$fiterdata['meta']['fields'][$col],$dgcols);
            }
            $result['dgcolumns'] = $dgcols;
        } else {
            $result['dgcolumns'] = array(
                'vh_id'=>array('label'=>'Vehicle ID','type'=>'link','visible'=>true, 'href'=>'/transport/viewvehicle'),
                'vh_no'=>array('label'=>'Vehicle No.','type'=>'string','visible'=>true),
                'vh_reg_no'=>array('label'=>'Reg. No.','type'=>'string','visible'=>true),
                'route_name'=>array('label'=>'Route','type'=>'string','visible'=>true), 
                'vh_driver_name'=>array('label'=>'Driver','type'=>'string','visible'=>true),
                'vh_reg_expiry'=>array('label'=>'Reg. Expiry','type'=>'string','visible'=>true),
                'vh_first_reg_date'=>array('label'=>'First Reg. Date','type'=>'string','visible'=>true),
                'vh_capacity'=>array('label'=>'Capacity','type'=>'string','visible'=>true),
                'vh_owner'=>array('label'=>'Owner','type'=>'string','visible'=>true),  
                'vh_owner_tel'=>array('label'=>'Owner Tel.','type'=>'string','visible'=>true),
                'ss_name'=>array('label'=>'Status','type'=>'string','visible'=>true)             
            );
        }        
        
        $result['actions'] = array(
            'edit'=>'/transport/editvehicle',
            'view'=>'/transport/viewvehicle',
            'page'=>'/transport/vehiclelist'
        );
        
        $offset = $result['rows_per_page']*($result['current_page']-1);
        $result['pages_total'] =  ceil($result['total']/$result['rows_per_page']);
        if($fiterdata=='')
            $result["dgitems"] = $this->get_vh_filter_list($result['rows_per_page'], $offset, '');
        else
            $result["dgitems"] = $this->get_vh_filter_list($result['rows_per_page'], $offset, $fiterdata);
        //print_object($result["dgcolumns"]); exit;
        return $result;
    }
    
    function build_area_dg_data($fiterdata='') {
        $result['dg_title'] = "Area List";
        if($this->input->post('show_all')) {
            $result['show_all'] = true;
        } else {
            //$result['show_all'] = false;
            $result['current_page'] = ($this->input->post('page')) ? $this->input->post('page') : 1;
            $result['rows_per_page'] = ($this->config->item('per_page')) ? $this->config->item('per_page') : 10;
        }
        if($fiterdata=='')
            $result['total'] = $this->get_area_filter_count();
        else
            $result['total'] = $this->get_area_filter_count($fiterdata);
            
        if($result['show_all']) {
            $result['current_page'] = 1;
            $result['rows_per_page'] = ($result['total']==0)?100:$result['total'];
        }
        
        $result['pk'] = 'area_id';
        $result['itemname'] = 'area_name';
        //print_object($fiterdata); exit;
        if(is_array($fiterdata['cols']) && count($fiterdata['cols']) > 0) {
            $dgcols = array(
                'area_id'=>array('label'=>'Area ID','type'=>'link','visible'=>true, 'href'=>'/transport/viewarea')
            );
            foreach($fiterdata['cols'] as $col) {
                $dgcol = $this->add_dg_col($col,$fiterdata['meta']['fields'][$col],$dgcols);
            }
            $result['dgcolumns'] = $dgcols;
        } else {
            $result['dgcolumns'] = array(
                'area_id'=>array('label'=>'Area ID','type'=>'link','visible'=>true, 'href'=>'/transport/viewarea'),
                'area_name'=>array('label'=>'Area Name','type'=>'string','visible'=>true),
                'area_code'=>array('label'=>'Area Code','type'=>'string','visible'=>true),
                'area_transport_fee'=>array('label'=>'Transport Fee','type'=>'string','visible'=>true)           
            );
        }        
        
        $result['actions'] = array(
            'edit'=>'/transport/editarea',
            'view'=>'/transport/viewarea',
            'page'=>'/transport/arealist'
        );
        
        $offset = $result['rows_per_page']*($result['current_page']-1);
        $result['pages_total'] =  ceil($result['total']/$result['rows_per_page']);
        if($fiterdata=='')
            $result["dgitems"] = $this->get_area_filter_list($result['rows_per_page'], $offset, '');
        else
            $result["dgitems"] = $this->get_area_filter_list($result['rows_per_page'], $offset, $fiterdata);
        return $result;
    }
    
    function build_route_dg_data($fiterdata='') {
        $result['dg_title'] = "Route List";
        if($this->input->post('show_all')) {
            $result['show_all'] = true;
        } else {
            //$result['show_all'] = false;
            $result['current_page'] = ($this->input->post('page')) ? $this->input->post('page') : 1;
            $result['rows_per_page'] = ($this->config->item('per_page')) ? $this->config->item('per_page') : 10;
        }
        if($fiterdata=='')
            $result['total'] = $this->get_route_filter_count();
        else
            $result['total'] = $this->get_route_filter_count($fiterdata);
            
        if($result['show_all']) {
            $result['current_page'] = 1;
            $result['rows_per_page'] = ($result['total']==0)?100:$result['total'];
        }
        
        $result['pk'] = 'route_id';
        $result['itemname'] = 'route_name';
        //print_object($fiterdata); exit;
        if(is_array($fiterdata['cols']) && count($fiterdata['cols']) > 0) {
            $dgcols = array(
                'route_id'=>array('label'=>'Route ID','type'=>'link','visible'=>true, 'href'=>'/transport/viewroute')
            );
            foreach($fiterdata['cols'] as $col) {
                $dgcol = $this->add_dg_col($col,$fiterdata['meta']['fields'][$col],$dgcols);
            }
            $result['dgcolumns'] = $dgcols;
        } else {
            $result['dgcolumns'] = array(
                'route_id'=>array('label'=>'Route ID','type'=>'link','visible'=>true, 'href'=>'/transport/viewroute'),
                'route_name'=>array('label'=>'Route Name','type'=>'string','visible'=>true)         
            );
        }        
        
        $result['actions'] = array(
            'edit'=>'/transport/editroute',
            'view'=>'/transport/viewroute',
            'page'=>'/transport/routelist'
        );
        
        $offset = $result['rows_per_page']*($result['current_page']-1);
        $result['pages_total'] =  ceil($result['total']/$result['rows_per_page']);
        if($fiterdata=='')
            $result["dgitems"] = $this->get_route_filter_list($result['rows_per_page'], $offset, '');
        else
            $result["dgitems"] = $this->get_route_filter_list($result['rows_per_page'], $offset, $fiterdata);
        return $result;
    }
    
    function get_vehiclestaff($data, $select="") {
        if($select!="") {
            $this->db->select($select);
        }
        $this->db->from('vehiclestaff vs');
        $this->db->join('vehiclestafftype vst','vs.vs_staff_type = vst.vst_id','left');
        $this->db->join('staffstatus ss','vs.vs_status = ss.ss_id','left');
        $this->db->join('country ct','vs.vs_nationality = ct.id','left');
        
        $this->db->where($data);
        $query = $this->db->get();
        $result = $query->result_array();
        //print_object($result); exit;
        return $result;
    }
    
    function get_vehicle($data, $select="") {
        if($select!="") {
            $this->db->select($select);
        } else {
            $this->db->select('vh.*, ru.*, ss.*, vsd.vs_name as vh_driver_name, vsh.vs_name as vh_helper_name');
        }
        //print_object($data);
        $this->db->from('vehicle vh');
        $this->db->join('route ru','vh.vh_route = ru.route_id','left');
        $this->db->join('staffstatus ss','vh.vh_status = ss.ss_id','left');
        $this->db->join('vehiclestaff vsd','vh.vh_driver = vsd.vs_id','left');   //Driver
        $this->db->join('vehiclestaff vsh','vh.vh_helper = vsh.vs_id','left');   //Helper
        
        $this->db->where($data);
        $query = $this->db->get();
        $result = $query->result_array();
        //print_object($result); exit;
        return $result;
    }
    
    function get_area($data, $select="") {
        if($select!="") {
            $this->db->select($select);
        }
        $this->db->from('area ar');
        
        $this->db->where($data);
        $query = $this->db->get();
        $result = $query->result_array();
        //print_object($result); exit;
        return $result;
    }
    
    function get_route($data, $select="") {
        if($select!="") {
            $this->db->select($select);
        }
        $this->db->from('route ru');
        
        $this->db->where($data);
        $query = $this->db->get();
        $result = $query->result_array();
        if(count($result) >0) {
            $i = 0;
            foreach($result as $row) {
                $result[$i]['route_area'] = $this->get_route_area($row['route_id']);
                $i++;
            }
        }
        //print_object($result); exit;
        return $result;
    }
    
    function get_route_area($route_id) {
        $area_list = array();
        $this->db->select('area_id');
        $this->db->from('routearea ra');
        $this->db->where(array('route_id'=>$route_id));
        $query = $this->db->get();
        $result = $query->result_array();
        foreach($result as $row) {
            array_push($area_list,$row['area_id']);
        }
        return $area_list;
    }
    
    function get_routes() {
        $query = $this->db->get('route');
        $result = $query->result_array();
        return $result;
    }
    
}

?>